﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Giaiphongmatbang.DataModel;

namespace Giaiphongmatbang.BLL
{
    public class HouseHoldRepository
    {
        private QLGPMBEntities data = new QLGPMBEntities();

        public IEnumerable<HouseHold> GetAll(int pageSize, int pageNumber, ref int totalRow)
        {
            var results = from p in data.HouseHolds
                          select p;
            totalRow = results.Count();
            return results.OrderByDescending(p=>p.HouseHoldID).Skip((pageNumber - 1) * pageSize).Take(pageSize);
        }

        public HouseHold GetByID(int ID)
        {
            var result = from p in data.HouseHolds
                         where p.HouseHoldID == ID
                         select p;
            return result.FirstOrDefault();
        }

        public HouseHold GetByCMND(string CMND)
        {
            return data.HouseHolds.Where(p => p.CMND == CMND).FirstOrDefault();
        }
        public IEnumerable<HouseHold> GetByDistrictID(int ID)
        {
            var results = from p in data.HouseHolds
                         where p.Village.District.DistrictID == ID
                         select p;
            return results;
        }

        public IEnumerable<HouseHold> GetByVillagesID(int ID)
        {
            var results = from p in data.HouseHolds
                          where p.Village.VillageID == ID
                          select p;
            return results;
        }

        public IEnumerable<HouseHold> GetByProjectID(int projectID, int pageSize, int pageNumber, ref int totalRow)
        {

            var results = data.HouseHolds.Where(p => p.Inventories.Any(q => q.Project.ProjectID == projectID));                
            totalRow = results.Count();
            return results.OrderBy(u=>u.FullName).Skip((pageNumber-1)*pageSize).Take(pageSize);
        }

        //Các hộ gia đình có tài sản nằm trong hơn i dự án
        //public IQueryable<HouseHold> GetMoreThan(int i)
        //{
        //    IQueryable<Inventory> inventory = from p in data.Inventories
        //                                      where 
                          
        //}

        //Tổng số hộ dân thuộc một xã nằm trong các dự án
        //public int CountHouseHolds(int villageID)
        //{
        //    var results = from p in data.HouseHolds
        //                  where p.Village.VillageID == villageID && data.Inventories.Any(q => q.CMND)
        //                  select p;
        //}

        public void Edit(ref HouseHold entity, string FullName, bool Gender, string CMND, string PhoneNumber, int? MemberCount, string Address, int VillageID)
        {
            entity.FullName = FullName;
            entity.Gender = Gender;
            entity.CMND = CMND;
            entity.PhoneNumber = PhoneNumber;
            entity.MemberCount = MemberCount;
            entity.Address = Address;
            entity.VillageID = VillageID;
            data.SaveChanges();
        }

        public void Insert(HouseHold h)
        {
            data.AddToHouseHolds(h);
        }

        public void Insert(string _CMND, string _FullName, string _Address, bool _Gender, string _PhoneNumber, int _memberCount, int _VillageID)
        {
            data.uspHouseHoldInsert(_CMND, _FullName, _Address, _Gender, _PhoneNumber, _memberCount, _VillageID);
        }
        public void DeleteByID(int id)
        {
            var obj = GetByID(id);
            if (obj != null)
            {
                data.DeleteObject(obj);
            }
            data.SaveChanges();
        }

        public void DeleteByCMND(string CMND)
        {
            var obj = GetByCMND(CMND);
            if (obj != null)
            {
                data.DeleteObject(obj);
            }
            data.SaveChanges();
        }
        public void DeleteFromProject(int ProjectID, string CMND)
        {
            int count = data.Inventories.Where(p => p.CMND.Equals(CMND)).Count();
            if (count <= 1) DeleteByCMND(CMND);
            else
            {
                var invent = data.Inventories.FirstOrDefault(p => p.ProjectID == ProjectID && p.CMND.Equals(CMND));
                data.DeleteObject(invent);
                var support = data.Supports.FirstOrDefault(p => p.ProjectID == ProjectID && p.CMND.Equals(CMND));
                data.DeleteObject(support);
                data.SaveChanges();
            }
        }

        public void DeleteFromProject(int ProjectID, int HouseHoldID)
        {
            string CMND = data.HouseHolds.FirstOrDefault(p => p.HouseHoldID == HouseHoldID).CMND;
            int count = data.Inventories.Where(p => p.CMND.Equals(CMND)).Count();
            if (count <= 1) DeleteByCMND(CMND);
            else
            {
                var invent = data.Inventories.FirstOrDefault(p => p.ProjectID == ProjectID && p.CMND.Equals(CMND));
                data.DeleteObject(invent);
                var support = data.Supports.FirstOrDefault(p => p.ProjectID == ProjectID && p.CMND.Equals(CMND));
                data.DeleteObject(support);
                data.SaveChanges();
            }
        }
        public void Save()
        {
            data.SaveChanges();
        }
    }
}